CREATE TABLE db_version (
  db  VARCHAR(20) NOT NULL,
  ver INT4        NOT NULL
);

INSERT INTO db_version VALUES ('julias', 1);

CREATE OR REPLACE FUNCTION assert_version(text, int4)
RETURNS int4 AS '
DECLARE
    ver_row db_version%ROWTYPE;
BEGIN
  SELECT * INTO ver_row FROM db_version WHERE db = $1 AND ver = $2;
  IF NOT FOUND THEN
    RAISE EXCEPTION $$Not found version %$$, $2;
  END IF;
  RETURN $2 + 1;
END
' LANGUAGE plpgsql;

CREATE TABLE buyer (
  buyer_id        SERIAL8 PRIMARY KEY,
  name            VARCHAR(20)  NOT NULL,
  email           VARCHAR(50)  NOT NULL,
  shipmentAddress VARCHAR(250) NOT NULL
);

CREATE TABLE product (
  product_id SERIAL8 PRIMARY KEY,
  title      VARCHAR(50) NOT NULL,
  price      INT4        NOT NULL
);

CREATE TABLE payment (
  payment_id SERIAL8 PRIMARY KEY,
  info       VARCHAR(150) NOT NULL,
  amount     INT4         NOT NULL
);

CREATE TABLE orders (
  order_id   SERIAL8 PRIMARY KEY,
  buyer_id   INT8 NOT NULL,
  payment_id INT8 NOT NULL,
  order_sum  INT8 NOT NULL,
  FOREIGN KEY (buyer_id) REFERENCES buyer (buyer_id),
  FOREIGN KEY (payment_id) REFERENCES payment (payment_id)
);

CREATE TABLE order_product (
  order_id   INT8,
  product_id INT8,
  PRIMARY KEY (order_id, product_id),
  FOREIGN KEY (order_id) REFERENCES orders (order_id) ON DELETE CASCADE ON UPDATE RESTRICT,
  FOREIGN KEY (product_id) REFERENCES product (product_id) ON DELETE CASCADE ON UPDATE RESTRICT
);

CREATE INDEX order_buyer_id_idx ON orders (buyer_id);
CREATE INDEX order_payment_id_idx ON orders (payment_id);




